﻿using Corina.Datamodel.Entities;
using Corina.Enumerations;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace Corina.DataContext.Service
{
    public class UserService : ServiceBase
    {
        public DataTable GetPendingUser(string connectionString)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT tu.*, tuk.Title AS UserKind, ti.Title AS Industry FROM t_user AS tu ");
            builder.Append("LEFT JOIN CORINA_BASE.dbo.t_user_kind AS tuk ON tuk.Id = tu.UserKindId ");
            builder.Append("LEFT JOIN CORINA_BASE.dbo.t_industry AS ti ON ti.Id = tu.IndustryId ");
            builder.Append("WHERE tu.AccountStatus = @Status");

            return DbOperator.GetDataTable(connectionString, builder.ToString(), new SqlParameter("@Status", (int)AccountStatusValue.Pending));
        }

        public bool UpdateStatus(string connectionString, int userId, AccountStatusValue status)
        {
            return DbOperator.UpdateEntityById<UserInfo>(connectionString, userId, new List<string>() { "AccountStatus" }, new List<object>() { (int)status });
        }

        public bool UpdatePassword(string connectionString, string account, string password)
        {
            string queryString = "UPDATE t_user SET [Password] =@Password WHERE Account=@Account";
            List<SqlParameter> paras = new List<SqlParameter>();
            paras.Add(new SqlParameter("@Password", password));
            paras.Add(new SqlParameter("@Account", account));

            return DbOperator.UpdateEntity(connectionString, queryString, paras.ToArray());
        }

        public DataTable GetAllItems(string connectionString, string key, int index, int size, ref int count)
        {
            List<SqlParameter> paras = new List<SqlParameter>();
            string qstring = "SELECT * FROM ( ";
            string cstring = "SELECT COUNT(*) FROM ( ";
            StringBuilder builder = new StringBuilder();

            builder.Append("SELECT ROW_NUMBER() OVER (ORDER BY tu.Id) AS ROW_NUMBER,tu.*,tuk.Title AS UserKind,tul.Title AS UserLevel, ti.Title AS Industry ");
            builder.Append("FROM t_user AS tu ");
            builder.Append("LEFT JOIN CORINA_BASE.dbo.t_user_kind AS tuk ON tuk.Id = tu.UserKindId ");
            builder.Append("LEFT JOIN CORINA_BASE.dbo.t_user_level AS tul ON tul.Id = tu.UserLevelId ");
            builder.Append("LEFT JOIN CORINA_BASE.dbo.t_industry AS ti ON ti.Id = tu.IndustryId ");
            builder.Append("WHERE 1=1 ");

            #region Key
            builder.Append("AND tu.Account LIKE @Key OR tu.Name LIKE @Key OR tu.Linkman LIKE @Key ");
            paras.Add(new SqlParameter("@Key", string.Format("%{0}%", key)));
            #endregion

            //builder.Append(" AND (tdm.DeviceSn LIKE @Key OR measure.Name LIKE @Key OR tdk.Name LIKE @Key ) ");
            builder.Append(") AS query ");

            count = DbOperator.GetCount(connectionString, string.Format("{0}{1}", cstring, builder), paras.ToArray());

            builder.Append("WHERE query.[ROW_NUMBER] BETWEEN @Index + 1 AND @Index + @Size ");
            paras.Add(new SqlParameter("@Index", (index - 1) * size));
            paras.Add(new SqlParameter("@Size", size));
            builder.Append("ORDER BY query.[ROW_NUMBER]");

            DataTable table = DbOperator.GetDataTable(connectionString, string.Format("{0}{1}", qstring, builder), paras.ToArray());
            return table;
        }

        public bool Update(string connectionString, UserInfo entity, EntityOperation operation)
        {
            return DbOperator.UpdateEntity(connectionString, entity, operation);
        }

        public bool ChangeUserLevel(string connectionString, int userId, int levelId)
        {
            return DbOperator.UpdateEntityById<UserInfo>(connectionString, userId, new List<string>() { "UserLevelId" }, new List<object>() { levelId });
        }
    }
}
